﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace hxlxc
{
    public partial class Form1Window : Form
    {

        DataTable dt1 = new DataTable();
        DataTable dt2 = new DataTable();
        DataTable dt_tiaojian = new DataTable();
        DataTable dt_jiegou = new DataTable();
        DataTable dt1_copy;

        // 下拉框中的第一个选项
        const string COMBOX_ALL_STR = "--All--";
        // 缓存当前选择的下拉值 cachedSelItem
        string CACHED_SEL_ITEM = COMBOX_ALL_STR;

        public Form1Window()
        {
            InitializeComponent();
        }

        private string getfilename()
        {
            System.Windows.Forms.OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.InitialDirectory = "c:\\";
            openFileDialog.Filter = "EXCEL文件|*.xls|所有文件|*.*";   //文本文件|*.*|C#文件|*.cs|所有文件|*.*|         
            openFileDialog.RestoreDirectory = true;
            openFileDialog.FilterIndex = 1;
            string filename = "";
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {

                filename = openFileDialog.FileName;

            }
            return filename;
        }

        // 
        private string translateString(string str)
        {
            Encoding utf8;
            utf8 = Encoding.GetEncoding("UTF-8");
            //Encoding gb2312 = Encoding.GetEncoding("GB2312");
            Encoding defaultEncoding = Encoding.GetEncoding(System.Text.Encoding.Default.HeaderName); // gb2312
            byte[] gb = defaultEncoding.GetBytes(str);
            gb = Encoding.Convert(defaultEncoding, utf8, gb);
            return utf8.GetString(gb);
        }


        private void Form1Window_SizeChanged(object sender, EventArgs e)
        {
            // From : https://bbs.csdn.net/topics/240036433
            if (this.WindowState == FormWindowState.Maximized) {
                //Console.WriteLine("222");
                //this.splitContainer1.PerformAutoScale();
                //this.splitContainer1.PerformLayout();
                //this.splitContainer1.Panel2.PerformLayout();
                //this.tabPage1Excel.PerformLayout();
            }
        }

        // 被排除的“字段”: 解决“开单导购”这样的添加字段的问题
        private List<string> GetExcludeFields()
        {
            List<string> list = new List<string>();

            string path = Environment.CurrentDirectory + "\\xc.ini";

            IniUtils IniUtils = new IniUtils(path);
            string FieldsExclude = IniUtils.IniReadValue("FieldsExclude", "Fields");
            if (FieldsExclude != null)
            {
                string[] arr = FieldsExclude.Split(',');
                foreach (string str in arr)
                {
                    string str_trim = str.Trim();
                    if (!str_trim.Equals(""))
                    {
                        list.Add(str.Trim());
                    }
                }
            }
            return list;
        }

        // 被排除的“字段”: 解决“开单导购”这样的添加字段的问题
        private string Get_section_key(string Section, string Key)
        {
            List<string> list = new List<string>();

            string path = Environment.CurrentDirectory + "\\xc.ini";

            IniUtils IniUtils = new IniUtils(path);
            return IniUtils.IniReadValue(Section, Key);
        }


        // 页签一，选择文件-btn
        private void btnExcelSelFile_Click(object sender, EventArgs e)
        {
            this.dataGridView1Excel.DataSource = null;
            this.textBox1Excel.Text = "";
            //this.cbb1.Items.Clear();
            this.textBox1Excel.Text = getfilename();
        }

        // 页签一，导入数据-btn
        private void btnExcelImport_Click(object sender, EventArgs e)
        {

            // 读取excel
            if (null != this.textBox1Excel.Text && this.textBox1Excel.Text.Length > 0)
            {
                try
                {
                    dt1 = excle_exp_or_imp.ExcelDataSet(this.textBox1Excel.Text, excle_exp_or_imp.GetExcelSheetName(this.textBox1Excel.Text));
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                    return;
                }
                this.dataGridView1Excel.DataSource = dt1;

                // 获取数据库表中当前最大的`batch_id`
                int maxBatchId = MySqlConn.GetMaxBatchId();

                List<string> insertSqlList = new List<string>();

                // 不需要合并的“字段”
                List<string> specialFileds = new List<string>();
                specialFileds.Add("订单总金额");
                specialFileds.Add("实收金额");
                specialFileds.Add("应收运费");
                specialFileds.Add("实收运费");
                int order_time_index = -1;
                int payment_status_index = -1;

                // 特殊“字段”对应的列序号(index)
                List<int> columnIndexs = new List<int>();

                // 被排除的字段的索引
                List<int> ExcludeFieldsIdx = new List<int>();
                List<string> ExcludeFields = GetExcludeFields();

                StringBuilder res1 = new StringBuilder();
                res1.Append("insert into excel_import_tbl_02(");
                for (int i = 0; i < dt1.Columns.Count; i++)
                {
                    string columnName = translateString(dt1.Columns[i].ColumnName.ToString());
                    if (specialFileds.Contains(columnName))
                    {
                        // 【添加】特殊字段对应的列序号
                        columnIndexs.Add(i);
                    }
                    if ("下单时间".Equals(columnName))
                    {
                        order_time_index = i;
                    }

                    if ("支付状态".Equals(columnName))
                    {
                        payment_status_index = i;
                    }

                    if (ExcludeFields.Contains(columnName))
                    {
                        // 被排除的字段-不要拼接
                        ExcludeFieldsIdx.Add(i);
                    }
                    else
                    {
                        res1.Append("`");
                        res1.Append(columnName);
                        res1.Append("`");
                        res1.Append(",");
                        //if (i != dt1.Columns.Count - 1) {
                        //    res1.Append(",");
                        //}
                    }


                }
                // 额外字段: `createa_date`, `batch_id`
                res1.Append("createa_date,");
                res1.Append("batch_id");
                res1.Append(") values(");
                string head = res1.ToString();

                Console.WriteLine(head);

                StringBuilder res = new StringBuilder();

                // 缓存上一行的值
                // List<string> cachedRow = new List<string>();
                string[] cachedRow = new string[dt1.Columns.Count];

                DateTime order_time;
                string end_time = Get_section_key("order_end_time", "end_time");
                String s_end_date;
                String s_start_date;

                DateTime end_date = DateTime.Now;
                DateTime start_date = DateTime.Now;
                Boolean insert_flag;

                String payment_status;

                String order_time_check_flag = Get_section_key("order_time_check", "order_time_check_flag");

                if ("Y".Equals(order_time_check_flag))
                {
                    s_end_date = this.dateTimePicker1Excel.Value.ToString("yyyy-MM-dd") + " " + end_time;
                    s_start_date = this.dateTimePicker1Excel.Value.AddDays(-1).ToString("yyyy-MM-dd") + " " + end_time;

                    end_date = DateTime.Parse(s_end_date);
                    start_date = DateTime.Parse(s_start_date);
                }

                // === 嵌套for循环 ===
                for (int i = 0; i < dt1.Rows.Count; i++)
                {
                    insert_flag = true;
                    res.Append(head);
                    object[] arr = dt1.Rows[i].ItemArray;
                    // dt1.Rows[i-1].ItemArray
                    for (int j = 0; j < arr.Length; j++)
                    {
                        string fieldValue = translateString(arr[j].ToString());
                        //如果下单时间不是当天22点以及之前到前天的22点以后，则数据不导入
                        //if ("Y".Equals(order_time_check_flag))
                        if(this.checkBox1OrderTimeCheck.Checked)
                        {
                            if (order_time_index == j)
                            {
                                if ("".Equals(fieldValue))
                                {
                                    order_time = DateTime.Parse(cachedRow[j].ToString());
                                }
                                else
                                {
                                    order_time = DateTime.Parse(fieldValue);
                                }
                                if (!(start_date < order_time && order_time <= end_date))
                                {
                                    insert_flag = false;
                                    if (!"".Equals(arr[0].ToString()))
                                    {
                                        cachedRow[j] = fieldValue;
                                    }
                                    break;
                                }
                            }
                        }

                        //如果支付状态不是已支付，则数据不导入
                        if (payment_status_index == j)
                        {
                            if ("".Equals(fieldValue))
                            {
                                payment_status = cachedRow[j].ToString();
                            }
                            else
                            {
                                payment_status = fieldValue;
                            }
                            if (!"已支付".Equals(payment_status))
                            {
                                insert_flag = false;
                                if (!"".Equals(arr[0].ToString()))
                                {
                                    cachedRow[j] = fieldValue;
                                }
                                break;
                            }
                        }

                        // 对边界引号做转义处理
                        if (fieldValue.Contains("'"))
                        {
                            fieldValue = fieldValue.Replace("'", "''");
                        }

                        // 如果第一列为空（即"订单编号"为空）就表明此行与上一行(或上两行, N行)是合并行。
                        if ("".Equals(arr[0].ToString()))
                        {
                            if (fieldValue.Equals("") && !columnIndexs.Contains(j))
                            {
                                // 如果此列为空，并且此字段不是"特殊字段"，将用"缓存行"的列替换此列!
                                fieldValue = cachedRow[j];
                            }
                            //  else { } 不做处理
                        }
                        else
                        {
                            // 如果"订单编号"不为空时，需要缓存此行，以应对下面一行或多行(合并行)的数据的获取。
                            cachedRow[j] = fieldValue;
                        }

                        // 对-不包含在“排除字段”中的字段-进行拼接
                        if (!ExcludeFieldsIdx.Contains(j))
                        {
                            res.Append("'");
                            res.Append(fieldValue);
                            res.Append("'");
                            res.Append(",");
                            //if (j != arr.Length - 1) {
                            //    res.Append(",");
                            //}
                        }

                    }
                    // 额外字段: `createa_date`, `batch_id`
                    // `createa_date` 从页面获取
                    if (insert_flag)
                    {
                        string createa_date = this.dateTimePicker1Excel.Value.ToString("yyyy-MM-dd");
                        res.Append("str_to_date('" + createa_date + "', '%Y-%m-%d'),");
                        res.Append(string.Concat((maxBatchId + 1)));
                        res.Append(")");

                        insertSqlList.Add(res.ToString());

                        Console.WriteLine(res.ToString());


                    }
                    // 重用同一个`StringBuilder`
                    res.Remove(0, res.Length);
                }
                try
                {
                    if (insertSqlList.Count > 0)
                    {
                        MySqlConn.BatchInsert(insertSqlList);
                        MessageBox.Show("导入成功");
                    }
                    else
                    {
                        MessageBox.Show("没有符合条件的数据导入进去");
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
            else
            {
                MessageBox.Show("请先选择要导入的Excel文件");
            }
        }

        private void checkBox1OrderTimeCheck_CheckedChanged(object sender, EventArgs e)
        {
            //CheckBox cb = (CheckBox)sender;
            //Console.WriteLine(cb.Checked); // True/False
            //Console.WriteLine(cb.CheckState); // Checked/Unchecked
        }

        private void tabPage1CommonQuery_Click(object sender, EventArgs e)
        {

        }

        // “供应商订单总数查询” - 查询所有
        private void Gysddzs_SelectAll(DateTimePicker dateTimePicker)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select a.createa_date,a.`商品编码`,a.`商品名称`,sum(a.`商品数量`) as `商品数量` from excel_import_tbl_02 a");
            sb.Append(" WHERE a.batch_id=(SELECT max(a1.batch_id) from excel_import_tbl_02 a1 where a1.createa_date = ");
            sb.Append(" '").Append(dateTimePicker.Value.ToString("yyyy-MM-dd")).Append("')");
            sb.Append(" group by a.createa_date, a.`商品编码`,a.`商品名称` order by a.createa_date,a.`商品编码`");

            DataSet dataset = MySqlConn.GetTotal_Number_Orders(sb.ToString());
            if (null != dataset && dataset.Tables.Count != 0)
            {
                this.dataGridView1TYSTotal.DataSource = dataset.Tables[0];
            }
        }

        // 页签“供应商订单总数查询” - 查询单一“商品编码”
        private void Gysddzs_SelectOne(string spbm, DateTimePicker dateTimePicker)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(" select a.createa_date,a.`商品编码`,a.`商品名称`,sum(a.`商品数量`) as `商品数量` ");
            sb.Append(" from excel_import_tbl_02 a ");
            sb.Append(" WHERE a.batch_id=(SELECT max(a1.batch_id) from excel_import_tbl_02 a1 ");
            sb.Append(" where a1.createa_date = ");
            sb.Append(" '").Append(dateTimePicker.Value.ToString("yyyy-MM-dd")).Append("')");
            sb.Append(" and a.`商品编码` = ");
            sb.Append("'").Append(spbm).Append("'");
            sb.Append(" group by a.createa_date, a.`商品编码`,a.`商品名称` order by a.createa_date,a.`商品编码`");

            DataSet dataset = MySqlConn.GetTotal_Number_Orders(sb.ToString());
            if (null != dataset && dataset.Tables.Count != 0)
            {
                this.dataGridView1TYSTotal.DataSource = dataset.Tables[0];
            }
        }

        // “供应商订单总数查询”
        private void btnGYSTotalQuery_Click(object sender, EventArgs e)
        {
            if (CACHED_SEL_ITEM.Equals(COMBOX_ALL_STR))
            {
                Gysddzs_SelectAll(this.dateTimePicker1GYSTotal);
            }
            else
            {
                Gysddzs_SelectOne(CACHED_SEL_ITEM, this.dateTimePicker1GYSTotal);
            }
        }

        // “供应商订单总数查询”-- 导出
        private void btnGYSTotalExport_Click(object sender, EventArgs e)
        {
            //ImportExportToExcel exp_excel = new ImportExportToExcel();
            //if (null != this.dataGridView5.DataSource)
            //{
            //    DataTable dataTable = (System.Data.DataTable)this.dataGridView5.DataSource;
            //    DateTime currentTime = DateTime.Now; ;
            //    exp_excel.doExport(dataTable, "d:\\" + "供应商订单总数" + this.dateTimePicker2.Value.ToString("yyyyMMdd")+ currentTime.Hour+ currentTime.Minute+ currentTime.Second+ currentTime.Millisecond + ".xls");
            //    MessageBox.Show("结果已导出");
            //}
            //else
            //{
            //    MessageBox.Show("请先查询");
            //}

            DateTime currentTime = DateTime.Now;
            string exportFilePath = "d:\\" + "供应商订单总数for自提点导出"
                + this.dateTimePicker1GYSTotal.Value.ToString("yyyyMMdd") + currentTime.Hour
                + currentTime.Minute + currentTime.Second + currentTime.Millisecond + ".xls";

            string selectDateStr = this.dateTimePicker1GYSTotal.Value.ToString("yyyy-MM-dd");
            List<DataTable> dataTableList = new List<DataTable>();
            // 既是“sheetName集合”也是自提单集合
            string currentSel_GYS = COMBOX_ALL_STR;
            List<string> GYS_List = new List<string>();
            if (this.comboBox1GYSTotal.SelectedItem != null)
            {
                currentSel_GYS = this.comboBox1GYSTotal.SelectedItem.ToString();
            }
            if (currentSel_GYS.Equals(COMBOX_ALL_STR))
            {
                GYS_List = CommonGetSPBM_ByDate(selectDateStr); // 供应商list 
                foreach (string spbm in GYS_List)
                {
                    dataTableList.Add(FillDataSet_SelectOne_By_ZTD(spbm, this.dateTimePicker1GYSTotal));
                }
            }
            else
            {
                GYS_List.Add(currentSel_GYS);
                // 导出一个供应商
                dataTableList.Add(FillDataSet_SelectOne_By_ZTD(currentSel_GYS, this.dateTimePicker1GYSTotal));
            }


            new MyExcelTools().ExportMultiSheets(dataTableList, exportFilePath, GYS_List);

            MessageBox.Show("已导出");
        }


        //   "团长收益查询"
        private void btnTZSYQuery_Click(object sender, EventArgs e)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(" select a2.createa_date, a2.`自提点`,sum(a2.`销售额`) as 销售额, ROUND(sum(a2.`销售额`)*0.1, 2) as 团长收益");
            sb.Append(" from (select  a1.`自提点`,ROUND(sum(a1.`商品数量`)*a1.`商品单价`,2) as 销售额, a1.createa_date ");
            sb.Append(" from excel_import_tbl_02 a1 ");
            sb.Append(" where a1.batch_id = (select max(a.batch_id) ");
            sb.Append(" from excel_import_tbl_02 a where createa_date = ");
            sb.Append("  str_to_date('").Append(this.dateTimePicker1TZSY.Value.ToString("yyyy-MM-dd")).Append("', '%Y-%m-%d'))");
            sb.Append(" GROUP BY a1.`自提点`,a1.`商品名称`,a1.`商品单价`, a1.createa_date ) a2 group by  a2.`自提点`,a2.createa_date");
            sb.Append(" union ALL");
            sb.Append(" SELECT a3.createa_date, '[汇总]' as 自提点, sum( a3.`销售额`) as 销售额,sum( a3.`团长收益`) as 团长收益 from (");
            sb.Append(" select a2.createa_date, a2.`自提点`,sum(a2.`销售额`) as 销售额, ROUND(sum(a2.`销售额`)*0.1, 2) as 团长收益");
            sb.Append(" from (select  a1.`自提点`,ROUND(sum(a1.`商品数量`)*a1.`商品单价`,2) as 销售额, a1.createa_date ");
            sb.Append(" from excel_import_tbl_02 a1 ");
            sb.Append(" where a1.batch_id = (select max(a.batch_id) ");
            sb.Append(" from excel_import_tbl_02 a where createa_date = ");
            sb.Append("  str_to_date('").Append(this.dateTimePicker1TZSY.Value.ToString("yyyy-MM-dd")).Append("', '%Y-%m-%d'))");
            sb.Append(" GROUP BY a1.`自提点`,a1.`商品名称`,a1.`商品单价`, a1.createa_date ) a2 group by  a2.`自提点`,a2.createa_date) a3 ");
            sb.Append(" group by a3.createa_date");

            DataSet dataset = MySqlConn.GetTotal_Number_Orders(sb.ToString());
            if (null != dataset && dataset.Tables.Count != 0)
            {
                this.dataGridView1TZSY.DataSource = dataset.Tables[0];
            }
        }

        // "团长收益查询" 导出
        private void btnTZSYExport_Click(object sender, EventArgs e)
        {
            ImportExportToExcel exp_excel = new ImportExportToExcel();
            if (null != this.dataGridView1TZSY.DataSource)
            {
                DataTable dataTable = (System.Data.DataTable)this.dataGridView1TZSY.DataSource;

                DateTime currentTime = DateTime.Now;

                string filePath = "d:\\" + "团长收益查询"
                    + this.dateTimePicker1TZSY.Value.ToString("yyyyMMdd")
                    + currentTime.Hour + currentTime.Minute + currentTime.Second
                    + currentTime.Millisecond + ".xls";

                exp_excel.doExport(dataTable, filePath);
                MessageBox.Show("结果已导出");
            }
            else
            {
                MessageBox.Show("请先查询");
            }
        }




        // 送货单总数查询
        private void btnSHD_Query_Click(object sender, EventArgs e)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(" select  a5.`自提点`,a5.`商品名称`,a5.`商品编码`,a5.`商品数量`,a5.`商品单价`,a5.`商品销售额` ");
            sb.Append(" from ( ");
            sb.Append("     select a1.`商品编码`,a1.`商品名称`, a1.`自提点`,CAST(sum(a1.`商品数量`) AS CHAR) as 商品数量,CAST(a1.`商品单价` as CHAR) as 商品单价,ROUND(sum(a1.`商品数量`)*a1.`商品单价`,2) as 商品销售额");
            sb.Append("     from excel_import_tbl_02 a1 ");
            sb.Append("     where a1.batch_id = (");
            sb.Append("         SELECT max(a.batch_id) from excel_import_tbl_02 a where createa_date = ");
            sb.Append("  '").Append(this.dateTimePicker1SHD.Value.ToString("yyyy-MM-dd")).Append("')");
            sb.Append("         GROUP BY a1.`商品编码`,a1.`自提点`,a1.`商品名称`,a1.`商品单价` order by a1.`自提点`,a1.`商品编码` ) a5");
            sb.Append(" union all ");
            sb.Append(" select  a5.`自提点`,a5.`商品名称`,a5.`商品编码`,a5.`商品数量`,a5.`商品单价`,a5.`商品销售额` ");
            sb.Append(" from (");
            sb.Append("     select a4.`商品编码` ,a4.`商品名称`,a4.`自提点`,a4.`商品数量`,a4.`商品单价`,a4.`商品销售额` ");
            sb.Append("     from  (");
            sb.Append("         select '销售额' as `商品名称`,a3.`自提点`,''as `商品数量` ,'' as 商品单价 ,a3.`商品销售额`,''as`商品编码` ");
            sb.Append("         from ( select a2.`自提点`,sum(a2.`商品销售额`) as 商品销售额  ");
            sb.Append("         from (select a1.`商品名称`, a1.`自提点`,sum(a1.`商品数量`) as 商品数量,a1.`商品单价`,ROUND(sum(a1.`商品数量`)*a1.`商品单价`,2) as 商品销售额 ");
            sb.Append("         from excel_import_tbl_02 a1 ");
            sb.Append("         where a1.batch_id = (select max(a.batch_id) ");
            sb.Append("         from excel_import_tbl_02 a where createa_date = ");
            sb.Append("  '").Append(this.dateTimePicker1SHD.Value.ToString("yyyy-MM-dd")).Append("')");
            sb.Append("         GROUP BY a1.`自提点`,a1.`商品名称`,a1.`商品单价` ) a2 group by  a2.`自提点`) a3) a4 ");
            sb.Append("         order by a4.`自提点`,a4.`商品编码` ) a5");



            DataSet dataset = MySqlConn.GetTotal_Number_Orders(sb.ToString());
            if (null != dataset && dataset.Tables.Count != 0)
            {
                this.dataGridView1SHDTotal.DataSource = dataset.Tables[0];
            }
        }

        // 送货单总数-导出
        private void btnSHD_Export_Click(object sender, EventArgs e)
        {
            //ImportExportToExcel exp_excel = new ImportExportToExcel();
            //if (null != this.dataGridView2.DataSource)
            //{
            //    DataTable dataTable = (System.Data.DataTable)this.dataGridView2.DataSource;
            //    DateTime currentTime = DateTime.Now;
            //    exp_excel.doExport(dataTable, "d:\\" + "送货单总数" + this.dateTimePicker2.Value.ToString("yyyyMMdd") + currentTime.Hour + currentTime.Minute + currentTime.Second + currentTime.Millisecond + ".xls");
            //    MessageBox.Show("结果已导出");
            //}
            //else
            //{
            //    MessageBox.Show("请先查询");
            //}

            DateTime currentTime = DateTime.Now;
            string exportFilePath = "d:\\" + "送货单总数查询"
                + this.dateTimePicker1SHD.Value.ToString("yyyyMMdd") + currentTime.Hour
                + currentTime.Minute + currentTime.Second + currentTime.Millisecond + ".xls";

            string selectDateStr = this.dateTimePicker1SHD.Value.ToString("yyyy-MM-dd");

            List<DataTable> dataTableList = new List<DataTable>();
            // 既是“sheetName集合”也是自提单集合
            List<string> sheetNameList = CommonGetZTD_ByDate(selectDateStr);

            foreach (string ztd in sheetNameList)
            {
                dataTableList.Add(FillDataSet_By_ZTD002(ztd, selectDateStr));
            }
            new MyExcelTools().ExportMultiSheets(dataTableList, exportFilePath, sheetNameList);

            MessageBox.Show("已导出");
        }

        // 通用-查询“自提点”
        public List<string> CommonGetZTD_ByDate(string specifyDateStr)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(" SELECT a.`自提点` from excel_import_tbl_02 a");
            sb.Append(" where a.batch_id = (");
            sb.Append(" SELECT max(a1.batch_id) from excel_import_tbl_02 a1 ");
            sb.Append(" where a1.createa_date = ");
            sb.Append(" str_to_date('").Append(specifyDateStr).Append("', '%Y-%m-%d'))");
            sb.Append(" GROUP BY a.`自提点`");
            sb.Append(" ORDER BY a.`自提点`");

            DataSet dataset = MySqlConn.Common_Query(sb.ToString());

            DataTable dt = dataset.Tables[0];

            List<string> retList = null;

            if (dt != null && dt.Rows.Count > 0)
            {
                retList = new List<string>();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    retList.Add(dt.Rows[i][0].ToString());
                }
            }

            return retList;
        }

        // 根据“自提点”填充 DataTable for "送货单总数查询"
        private DataTable FillDataSet_By_ZTD002(string ztd, string strDate)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(" select  a5.`自提点`,a5.`商品名称`,a5.`商品编码`,a5.`商品数量`,a5.`商品单价`,a5.`商品销售额` ");
            sb.Append(" from ( ");
            sb.Append(" select a1.`商品编码`,a1.`商品名称`, a1.`自提点`,CAST(sum(a1.`商品数量`) AS CHAR) as 商品数量,CAST(a1.`商品单价` as CHAR) as 商品单价,ROUND(sum(a1.`商品数量`)*a1.`商品单价`,2) as 商品销售额");
            sb.Append(" from excel_import_tbl_02 a1 ");
            sb.Append(" where a1.batch_id = (");
            sb.Append(" SELECT max(a.batch_id) from excel_import_tbl_02 a where createa_date = ");
            sb.Append(" str_to_date('").Append(strDate).Append("', '%Y-%m-%d'))");
            sb.Append(" and a1.`自提点` =");
            sb.Append("'").Append(ztd).Append("'");
            sb.Append(" GROUP BY a1.`商品编码`,a1.`自提点`,a1.`商品名称`,a1.`商品单价` order by a1.`自提点`,a1.`商品编码` ) a5");
            sb.Append(" union all ");
            sb.Append(" select  a5.`自提点`,a5.`商品名称`,a5.`商品编码`,a5.`商品数量`,a5.`商品单价`,a5.`商品销售额` ");
            sb.Append(" from (");
            sb.Append(" select a4.`商品编码` ,a4.`商品名称`,a4.`自提点`,a4.`商品数量`,a4.`商品单价`,a4.`商品销售额` ");
            sb.Append(" from  (");
            sb.Append(" select '销售额' as `商品名称`,a3.`自提点`,''as `商品数量` ,'' as 商品单价 ,a3.`商品销售额`,''as`商品编码` ");
            sb.Append(" from ( select a2.`自提点`,sum(a2.`商品销售额`) as 商品销售额  ");
            sb.Append(" from (select a1.`商品名称`, a1.`自提点`,sum(a1.`商品数量`) as 商品数量,a1.`商品单价`,ROUND(sum(a1.`商品数量`)*a1.`商品单价`,2) as 商品销售额 ");
            sb.Append(" from excel_import_tbl_02 a1 ");
            sb.Append(" where a1.batch_id = (select max(a.batch_id) ");
            sb.Append(" from excel_import_tbl_02 a where createa_date = ");
            sb.Append(" str_to_date('").Append(strDate).Append("', '%Y-%m-%d'))");
            sb.Append(" and a1.`自提点` =");
            sb.Append("'").Append(ztd).Append("'");
            sb.Append(" GROUP BY a1.`自提点`,a1.`商品名称`,a1.`商品单价` ) a2 group by  a2.`自提点`) a3) a4 ");
            sb.Append(" order by a4.`自提点`,a4.`商品编码` ) a5");

            DataSet dataset = MySqlConn.Common_Query(sb.ToString());
            DataTable dt = null;
            if (null != dataset && dataset.Tables.Count != 0)
            {
                dt = dataset.Tables[0];
            }

            return dt;
        }

        // 切换页签时触发
        private void tabControl1Main_SelectedIndexChanged(object sender, EventArgs e)
        {
            ShowTips("[enter] 切换页签时触发");

            ShowTips(this.tabControl1Main.SelectedTab.Name);

            // “供应商订单总数查询”
            if (this.tabControl1Main.SelectedTab.Name == "tabPage5GYSTotalOrder")
            {
                DateTime currentTime = DateTime.Now;
                string specifyDateStr = currentTime.ToString("yyyy-MM-dd");
                Drddfhxx_ComboBox_setup(specifyDateStr, this.comboBox1GYSTotal);
            }

            // “送货单详细查询” for 供应商
            if (this.tabControl1Main.SelectedTab.Name == "tabPage3forGYS")
            {
                DateTime currentTime = DateTime.Now;
                string specifyDateStr = currentTime.ToString("yyyy-MM-dd");
                Drddfhxx_ComboBox_setup(specifyDateStr, this.comboBox1forGYS);
            }

            ShowTips("[leave] 切换页签时触发");
        }

        // [公用] "导入订单复合信息" - 初始化下拉框
        private void Drddfhxx_ComboBox_setup(string specifyDateStr, ComboBox targetComboBox)
        {
            targetComboBox.Items.Clear();
            // `setup`时将缓存`CACHED_SEL_ITEM`赋值为"ALL"
            CACHED_SEL_ITEM = COMBOX_ALL_STR;

            List<string> list = CommonGetSPBM_ByDate(specifyDateStr);
            if (list != null && list.Count > 0)
            {
                targetComboBox.Items.Add(COMBOX_ALL_STR);
                foreach (string item in list)
                {
                    targetComboBox.Items.Add(item);
                }
            }
        }

        // 通用-查询“商品编码”
        public List<string> CommonGetSPBM_ByDate(string specifyDateStr)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(" SELECT a.`商品编码` from excel_import_tbl_02 a");
            sb.Append(" where a.batch_id = (");
            sb.Append(" SELECT max(a1.batch_id) from excel_import_tbl_02 a1 ");
            sb.Append(" where a1.createa_date = ");
            sb.Append(" str_to_date('").Append(specifyDateStr).Append("', '%Y-%m-%d'))");
            sb.Append(" GROUP BY a.`商品编码`");
            sb.Append(" ORDER BY a.`商品编码`");

            DataSet dataset = MySqlConn.Common_Query(sb.ToString());

            DataTable dt = dataset.Tables[0];

            List<string> retList = null;

            if (dt != null && dt.Rows.Count > 0)
            {
                retList = new List<string>();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    retList.Add(dt.Rows[i][0].ToString());
                }
            }

            return retList;
        }

        // “供应商订单总数查询”中的日期选择控件 - change事件
        private void dateTimePicker1GYSTotal_ValueChanged(object sender, EventArgs e)
        {
            DateTimePicker select_dateTime = (DateTimePicker)sender;
            string specifyDateStr = select_dateTime.Value.ToString("yyyy-MM-dd");
            Drddfhxx_ComboBox_setup(specifyDateStr, this.comboBox1GYSTotal);
        }

        // “供应商订单总数查询”-下拉框-change-事件
        private void comboBox1GYSTotal_SelectedIndexChanged(object sender, EventArgs e)
        {
            ComboBox combo = (ComboBox)sender;
            string selStr = combo.SelectedItem.ToString();

            // 缓存当前选择的下拉值
            CACHED_SEL_ITEM = selStr;

            if (selStr.Equals(COMBOX_ALL_STR))
            {
                Gysddzs_SelectAll(this.dateTimePicker1GYSTotal);
            }
            else
            {
                Gysddzs_SelectOne(selStr, this.dateTimePicker1GYSTotal);
            }
        }
        
        // for 供应商
        private void dateTimePicker1forGYS_ValueChanged(object sender, EventArgs e)
        {
            DateTimePicker select_dateTime = (DateTimePicker)sender;
            string specifyDateStr = select_dateTime.Value.ToString("yyyy-MM-dd");
            Drddfhxx_ComboBox_setup(specifyDateStr, this.comboBox1forGYS);
        }

        // for 供应商
        private void comboBox1forGYS_SelectedIndexChanged(object sender, EventArgs e)
        {
            ComboBox combo = (ComboBox)sender;
            string selStr = combo.SelectedItem.ToString();

            // 缓存当前选择的下拉值
            CACHED_SEL_ITEM = selStr;

            if (selStr.Equals(COMBOX_ALL_STR))
            {
                Shdxxzs_SelectAll(this.dateTimePicker1forGYS);
            }
            else
            {
                Shdxxzs_SelectOne(selStr, this.dateTimePicker1forGYS);
            }
        }

        // “送货单详细查询” - 查询所有
        private void Shdxxzs_SelectAll(DateTimePicker dateTimePicker)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(" select a.createa_date,a.`订单编号`,a.`商品名称`,a.`商品数量`,a.`自提点` ,a.`收货人姓名`,a.`用户昵称`,a.`收货人手机号`,a.`商品编码` ");
            sb.Append(" from excel_import_tbl_02 a");
            sb.Append("  WHERE a.batch_id=(SELECT max(a1.batch_id) from excel_import_tbl_02 a1 where a1.createa_date = ");
            sb.Append(" str_to_date('").Append(dateTimePicker.Value.ToString("yyyy-MM-dd")).Append("', '%Y-%m-%d'))");
            sb.Append("  order by a.batch_id,a.createa_date,a.`自提点`,a.`收货人手机号`,a.`收货人姓名`,a.`用户昵称`,a.`订单编号`,a.`商品编码`");

            Console.WriteLine(sb.ToString());

            DataSet dataset = MySqlConn.GetTotal_Number_Orders(sb.ToString());
            if (null != dataset && dataset.Tables.Count != 0)
            {
                this.dataGridView1forGYS.DataSource = dataset.Tables[0];
            }
        }

        // “送货单详细查询” - 查询单一“商品编码”
        private void Shdxxzs_SelectOne(string spbm, DateTimePicker dateTimePicker)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(" select a.createa_date,a.`订单编号`,a.`商品名称`,a.`商品数量`,a.`自提点` ,a.`收货人姓名`,a.`用户昵称`,a.`收货人手机号`,a.`商品编码` ");
            sb.Append(" from excel_import_tbl_02 a");
            sb.Append("  WHERE a.batch_id=(SELECT max(a1.batch_id) from excel_import_tbl_02 a1 where a1.createa_date = ");
            sb.Append(" str_to_date('").Append(dateTimePicker.Value.ToString("yyyy-MM-dd")).Append("', '%Y-%m-%d'))");
            sb.Append(" and a.`商品编码` = ");
            sb.Append("'").Append(spbm).Append("'");
            sb.Append("  order by a.batch_id,a.createa_date,a.`自提点`,a.`收货人手机号`,a.`收货人姓名`,a.`用户昵称`,a.`订单编号`,a.`商品编码`");

            Console.WriteLine(sb.ToString());

            DataSet dataset = MySqlConn.GetTotal_Number_Orders(sb.ToString());
            if (null != dataset && dataset.Tables.Count != 0)
            {
                this.dataGridView1forGYS.DataSource = dataset.Tables[0];
            }
        }

        private void ShowTips(string msg) {
            string prefix = DateTime.Now.ToLocalTime().ToString();
            if (msg != null)
            {
                this.richTextBox1ShowTip.AppendText(prefix + " : " + msg);
            }
            else {
                this.richTextBox1ShowTip.AppendText(prefix);
            }
            this.richTextBox1ShowTip.AppendText(Environment.NewLine);
            
        }

        // for自提点-查询
        private void btnforZTD_Click(object sender, EventArgs e)
        {
            ShowTips("[进入] for自提点-查询");

            StringBuilder sb = new StringBuilder();
            
            sb.Append(" select a.createa_date,a.`订单编号`,a.`商品名称`,a.`商品数量`,a.`自提点` ,a.`收货人姓名`,a.`用户昵称`,a.`收货人手机号` ");
            sb.Append(" from excel_import_tbl_02 a");
            sb.Append(" WHERE a.batch_id=(SELECT max(a1.batch_id) from excel_import_tbl_02 a1 where a1.createa_date = ");
            sb.Append(" str_to_date('").Append(this.dateTimePicker1forZTD.Value.ToString("yyyy-MM-dd")).Append("', '%Y-%m-%d'))");
            sb.Append(" order by a.batch_id,a.createa_date,a.`自提点`,a.`收货人手机号`,a.`收货人姓名`,a.`用户昵称`,a.`订单编号`");

            Console.WriteLine(sb.ToString());

            DataSet dataset = MySqlConn.Common_Query(sb.ToString());
            if (null != dataset && dataset.Tables.Count != 0)
            {
                this.dataGridView1forZTD.DataSource = dataset.Tables[0];
            }

            ShowTips("[离开] for自提点-查询");
        }

        // for自提点导出
        private void button1forZTD_Click(object sender, EventArgs e)
        {
            DateTimePicker dateTimePicker = this.dateTimePicker1forZTD;

            DateTime currentTime = DateTime.Now;
            string exportFilePath = "d:\\" + "送货单详细for自提点导出"
                + dateTimePicker.Value.ToString("yyyyMMdd") + currentTime.Hour
                + currentTime.Minute + currentTime.Second + currentTime.Millisecond + ".xls";

            string selectDateStr = dateTimePicker.Value.ToString("yyyy-MM-dd");
            List<DataTable> dataTableList = new List<DataTable>();
            // 既是“sheetName集合”也是自提单集合
            List<string> sheetNameList = CommonGetZTD_ByDate(selectDateStr);
            foreach (string ztd in sheetNameList)
            {
                dataTableList.Add(FillDataSet_By_ZTD(ztd, selectDateStr));
            }
            new MyExcelTools().ExportMultiSheets(dataTableList, exportFilePath, sheetNameList);

            MessageBox.Show("已导出");
        }

        // 根据“自提点”填充 DataTable
        private DataTable FillDataSet_By_ZTD(string ztd, string strDate)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" select a.createa_date,a.`订单编号`,a.`商品名称`,a.`商品数量`,a.`自提点` ,a.`收货人姓名`,a.`用户昵称`,a.`收货人手机号` ");
            sb.Append(" from excel_import_tbl_02 a");
            sb.Append(" WHERE a.batch_id=(SELECT max(a1.batch_id) from excel_import_tbl_02 a1 where a1.createa_date = ");
            sb.Append(" str_to_date('").Append(strDate).Append("', '%Y-%m-%d'))");
            sb.Append(" and a.`自提点` = ");
            sb.Append("'").Append(ztd).Append("'");
            sb.Append(" order by a.batch_id,a.createa_date,a.`自提点`,a.`收货人手机号`,a.`收货人姓名`,a.`用户昵称`,a.`订单编号`");
            DataSet dataset = MySqlConn.Common_Query(sb.ToString());
            DataTable dt = null;
            if (null != dataset && dataset.Tables.Count != 0)
            {
                dt = dataset.Tables[0];
            }

            return dt;
        }

        private DataTable FillDataSet_SelectOne_By_ZTD(string spbm, DateTimePicker dateTimePicker)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(" select a.createa_date,a.`商品编码`,a.`商品名称`,sum(a.`商品数量`) as `商品数量` ");
            sb.Append(" from excel_import_tbl_02 a ");
            sb.Append(" WHERE a.batch_id=(SELECT max(a1.batch_id) from excel_import_tbl_02 a1 ");
            sb.Append(" where a1.createa_date = ");
            sb.Append(" '").Append(dateTimePicker.Value.ToString("yyyy-MM-dd")).Append("')");
            sb.Append(" and a.`商品编码` = ");
            sb.Append("'").Append(spbm).Append("'");
            sb.Append(" group by a.createa_date, a.`商品编码`,a.`商品名称` order by a.createa_date,a.`商品编码`");

            DataSet dataset = MySqlConn.GetTotal_Number_Orders(sb.ToString());
            if (null != dataset && dataset.Tables.Count != 0)
            {
                return dataset.Tables[0];
            }
            return null;
        }

        // for供应商-查询
        private void btnforGYS_Click(object sender, EventArgs e)
        {
            if (CACHED_SEL_ITEM.Equals(COMBOX_ALL_STR))
            {
                Shdxxzs_SelectAll(this.dateTimePicker1forGYS);
            }
            else
            {
                Shdxxzs_SelectOne(CACHED_SEL_ITEM, this.dateTimePicker1forGYS);
            }
        }

        private void button1forGYS_Click(object sender, EventArgs e)
        {
            DateTimePicker dateTimePicker = this.dateTimePicker1forGYS;
            ComboBox comboBox = this.comboBox1forGYS;

            DateTime currentTime = DateTime.Now;
            string exportFilePath = "d:\\" + "送货单详细for供应商导出"
                + dateTimePicker.Value.ToString("yyyyMMdd") + currentTime.Hour
                + currentTime.Minute + currentTime.Second + currentTime.Millisecond + ".xls";

            string selectDateStr = dateTimePicker.Value.ToString("yyyy-MM-dd");

            List<DataTable> dataTableList = new List<DataTable>();

            List<string> sheetNameList = new List<string>();
            string currentSel_GYS = COMBOX_ALL_STR;
            if (comboBox.SelectedItem != null)
            {
                currentSel_GYS = comboBox.SelectedItem.ToString();
            }

            if (currentSel_GYS.Equals(COMBOX_ALL_STR))
            {
                List<string> GYS_List = CommonGetSPBM_ByDate(selectDateStr); // 供应商list
                sheetNameList = GYS_List;
                // 导出所有
                foreach (string gys in GYS_List)
                {
                    dataTableList.Add(FillDataSet_By_GYS(gys, selectDateStr));
                }
            }
            else
            {
                sheetNameList.Add(currentSel_GYS);
                // 导出一个供应商
                dataTableList.Add(FillDataSet_By_GYS(currentSel_GYS, selectDateStr));
            }

            new MyExcelTools().ExportMultiSheets(dataTableList, exportFilePath, sheetNameList);

            MessageBox.Show("已导出");
        }

        // 根据“商品编码”填充 DataTable
        private DataTable FillDataSet_By_GYS(string gys, string strDate)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" select a.createa_date,a.`订单编号`,a.`商品名称`,a.`商品数量`,a.`自提点` ,a.`收货人姓名`,a.`用户昵称`,a.`收货人手机号`,a.`商品编码` ");
            sb.Append(" from excel_import_tbl_02 a");
            sb.Append(" WHERE a.batch_id=(SELECT max(a1.batch_id) from excel_import_tbl_02 a1 where a1.createa_date = ");
            sb.Append(" str_to_date('").Append(strDate).Append("', '%Y-%m-%d'))");
            sb.Append(" and a.`商品编码` = ");
            sb.Append("'").Append(gys).Append("'");
            sb.Append(" order by a.batch_id,a.createa_date,a.`自提点`,a.`收货人手机号`,a.`收货人姓名`,a.`用户昵称`,a.`订单编号`,a.`商品编码`");
            DataSet dataset = MySqlConn.Common_Query(sb.ToString());
            DataTable dt = null;
            if (null != dataset && dataset.Tables.Count != 0)
            {
                dt = dataset.Tables[0];
            }

            return dt;
        }

        private void richTextBox1ShowTip_TextChanged(object sender, EventArgs e)
        {
            this.richTextBox1ShowTip.SelectionStart = this.richTextBox1ShowTip.Text.Length;
            this.richTextBox1ShowTip.ScrollToCaret();
        }
    }
}
